--STEP 4: DEPOSIT
 
--DROP TABLE #DAO
--select * FROM ANHCP.CEO_PERFORMANCE.DBO.DEPOSIT_EOP_20170430
 SELECT DAO 
INTO #DAO
FROM TBL_HR
WHERE GROUP_ID=4
AND DAO <> N'KHONG CO DAO'

--select * from ANHCP.CEO_PERFORMANCE.DBO.DEPOSIT_EOP_LOAI_QUICKWIN_20170228 where dao = '5587'
--select * from SANPHAM_DEPOSIT_M12 where dao = '5587'
DELETE FROM  #DAO WHERE DAO IS NULL
--SELECT * FROM DATE_EM_REPORT
DECLARE @MONTH1 DATE=DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,(SELECT MAX_DATE_ENDMONTH FROM DATE_EM_REPORT))-10,0))
declare @datecompare date = convert(date, '20170331')
--print @month1
DECLARE @COUNT INT =1
DECLARE @FULLDATE NVARCHAR(8)
WHILE @COUNT <13
BEGIN
	SET @FULLDATE= CONVERT (NVARCHAR(8), @MONTH1 ,112)
	DECLARE @BEGINDATE NVARCHAR(8) = CONVERT(NVARCHAR(8), DATEADD(M,-1, DATEADD(D, 1, @MONTH1)),112)
	--IF(@MONTH1 = '20151230') SET @FULLDATE = '20151231'
	IF(@MONTH1 = '20160131') SET @FULLDATE = '20160130'
	IF(@MONTH1 = '20160430') SET @FULLDATE = '20160429'
	IF(@MONTH1 = '20160731') SET @FULLDATE = '20160730'
	DECLARE @STRCOUNT NVARCHAR(2) = CONVERT(NVARCHAR(2),@COUNT)
	DECLARE @ENDDATE NVARCHAR(8) = @FULLDATE
	IF(@MONTH1 = '20160430') SET @ENDDATE = '20160430'

	declare @datediff int = (DATEDIFF(day,@dateCompare,@month1))
	--print @datediff
	--IF(@MONTH1 = '20151230') SET @ENDDATE = '20151231'
EXEC(
'IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''SANPHAM_DEPOSIT_M'+@STRCOUNT+''')
		DROP TABLE SANPHAM_DEPOSIT_M'+@STRCOUNT+'')



		IF (@datediff<0)
		exec('
			SELECT ''MONTH'+@STRCOUNT+''' AS YEARMONTH,WEEK_TO
			, CASE WHEN PRODUCT_NAME IN (''3. OTHER DEPOSIT'',''3. OTHERS'') THEN ''3. OTHER DEPOSIT'' ELSE PRODUCT_NAME END AS PRODUCT_NAME
			,DAO
			,SUM(BAL_QD) AS BAL_QD
			,COUNT(DISTINCT ACCTNO) AS COUNT_ACCOUNT 
			INTO SANPHAM_DEPOSIT_M'+@STRCOUNT+'
			FROM ANHCP.CEO_PERFORMANCE.DBO.DEPOSIT_EOP_LOAI_QUICKWIN_'+@FULLDATE+'
			WHERE DAO COLLATE DATABASE_DEFAULT IN (SELECT DAO FROM #DAO)
			GROUP BY DAO,WEEK_TO,CASE WHEN PRODUCT_NAME IN (''3. OTHER DEPOSIT'',''3. OTHERS'') THEN ''3. OTHER DEPOSIT'' ELSE PRODUCT_NAME END')
	
		IF @datediff>=0
		exec('
			SELECT ''MONTH'+@STRCOUNT+''' AS YEARMONTH,WEEK_TO
			, CASE WHEN PRODUCT_NAME IN (''3. OTHER DEPOSIT'',''3. OTHERS'') THEN ''3. OTHER DEPOSIT'' ELSE PRODUCT_NAME END AS PRODUCT_NAME
			,DAO
			,SUM(BAL_QD) AS BAL_QD
			,COUNT(DISTINCT ACCTNO) AS COUNT_ACCOUNT 
			INTO SANPHAM_DEPOSIT_M'+@STRCOUNT+'
			FROM ANHCP.CEO_PERFORMANCE.DBO.DEPOSIT_EOP_'+@FULLDATE+'
			WHERE DAO COLLATE DATABASE_DEFAULT IN (SELECT DAO FROM #DAO)
			AND QW=''N''
			GROUP BY DAO,WEEK_TO,CASE WHEN PRODUCT_NAME IN (''3. OTHER DEPOSIT'',''3. OTHERS'') THEN ''3. OTHER DEPOSIT'' ELSE PRODUCT_NAME END')
		
		exec('
IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''NEW_SANPHAM_DEPOSIT_M'+@STRCOUNT+''')
		DROP TABLE NEW_SANPHAM_DEPOSIT_M'+@STRCOUNT+'
		
SELECT WEEK_TO
, CASE WHEN PRODUCT_NAME IN (''3. OTHER DEPOSIT'',''3. OTHERS'') THEN ''3. OTHER DEPOSIT'' ELSE PRODUCT_NAME END AS PRODUCT_NAME
,DAO
,SUM(BAL_QD) AS BAL_QD
,COUNT(DISTINCT ACCTNO) AS COUNT_ACCOUNT 
INTO NEW_SANPHAM_DEPOSIT_M'+@STRCOUNT+'
FROM ANHCP.CEO_PERFORMANCE.DBO.DEPOSIT_MTD_'+@BEGINDATE+'_'+@ENDDATE+'
WHERE QUICKWIN=''N'' AND DAO COLLATE DATABASE_DEFAULT IN (SELECT DAO FROM #DAO)
GROUP BY DAO,WEEK_TO,CASE WHEN PRODUCT_NAME IN (''3. OTHER DEPOSIT'',''3. OTHERS'') THEN ''3. OTHER DEPOSIT'' ELSE PRODUCT_NAME END

') 

	--LAST DAY OF ANY MONTH AND YEAR
	SET @MONTH1 = DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,@MONTH1)+2,0))
--PRINT @MONTH1
SET @COUNT = @COUNT + 1
END


EXEC('
IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''SANPHAM_DEPOSIT'')
		DROP TABLE SANPHAM_DEPOSIT

SELECT * INTO SANPHAM_DEPOSIT FROM (
SELECT * FROM SANPHAM_DEPOSIT_M1
UNION ALL
SELECT * FROM SANPHAM_DEPOSIT_M2
UNION ALL
SELECT * FROM SANPHAM_DEPOSIT_M3
UNION ALL
SELECT * FROM SANPHAM_DEPOSIT_M4
UNION ALL
SELECT * FROM SANPHAM_DEPOSIT_M5
UNION ALL
SELECT * FROM SANPHAM_DEPOSIT_M6
UNION ALL
SELECT * FROM SANPHAM_DEPOSIT_M7
UNION ALL
SELECT * FROM SANPHAM_DEPOSIT_M8
UNION ALL
SELECT * FROM SANPHAM_DEPOSIT_M9
UNION ALL
SELECT * FROM SANPHAM_DEPOSIT_M10
UNION ALL
SELECT * FROM SANPHAM_DEPOSIT_M11
UNION ALL
SELECT * FROM SANPHAM_DEPOSIT_M12)A')




EXEC('

IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''NEW_SANPHAM_DEPOSIT'')
		DROP TABLE NEW_SANPHAM_DEPOSIT

SELECT * INTO NEW_SANPHAM_DEPOSIT FROM (
SELECT * FROM NEW_SANPHAM_DEPOSIT_M1
UNION ALL
SELECT * FROM NEW_SANPHAM_DEPOSIT_M2
UNION ALL
SELECT * FROM NEW_SANPHAM_DEPOSIT_M3
UNION ALL
SELECT * FROM NEW_SANPHAM_DEPOSIT_M4
UNION ALL
SELECT * FROM NEW_SANPHAM_DEPOSIT_M5
UNION ALL
SELECT * FROM NEW_SANPHAM_DEPOSIT_M6
UNION ALL
SELECT * FROM NEW_SANPHAM_DEPOSIT_M7
UNION ALL
SELECT * FROM NEW_SANPHAM_DEPOSIT_M8
UNION ALL
SELECT * FROM NEW_SANPHAM_DEPOSIT_M9
UNION ALL
SELECT * FROM NEW_SANPHAM_DEPOSIT_M10
UNION ALL
SELECT * FROM NEW_SANPHAM_DEPOSIT_M11
UNION ALL
SELECT * FROM NEW_SANPHAM_DEPOSIT_M12)A')
--SELECT distinct WEEK_TO FROM SANPHAM_DEPOSIT

--SELECT * FROM NEW_SANPHAM_DEPOSIT

--select * from SANPHAM_DEPOSIT where dao = '5587'

--delete from SP_DEPOSIT_CUSTOMER_DPTB_RAW
--drop table #deposit_raw
select CONVERT(nvarchar(6), CONVERT(DATE,WEEK_TO), 112) AS YEARMONTH, product_name,dao, SUM(COUNT_ACCOUNT) as quantity, sum(BAL_QD) as balance
 into #deposit_raw
 from SANPHAM_DEPOSIT 
 group by YEARMONTH, product_name, dao,WEEK_TO
 order by YEARMONTH, dao
 --drop table #new_deposit_raw
 
 --select distinct YEARMONTH from #deposit_raw

select  CONVERT(nvarchar(6), CONVERT(DATE,WEEK_TO), 112) AS YEARMONTH, product_name,dao, SUM(COUNT_ACCOUNT) as quantity, sum(BAL_QD) as balance
into #new_deposit_raw
 from NEW_SANPHAM_DEPOSIT
 group by  product_name, dao,WEEK_TO
 order by dao
 
delete from TBL_SP_DEPOSIT_EOP

declare @lastMonthDate date = (SELECT MAX_DATE_ENDMONTH FROM DATE_EM_REPORT)
--print CONVERT(NVARCHAR(6),(DATEADD(M, 0, @lastMonthDate)),112)


DECLARE @MONTH_1 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -1, @lastMonthDate)),112)
DECLARE @MONTH2 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -2,@lastMonthDate)),112)
DECLARE @MONTH3 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -3, @lastMonthDate)),112)
DECLARE @MONTH4 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -4, @lastMonthDate)),112)
DECLARE @MONTH5 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -5, @lastMonthDate)),112)
DECLARE @MONTH6 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -6, @lastMonthDate)),112)
DECLARE @MONTH7 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -7, @lastMonthDate)),112)
DECLARE @MONTH8 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -8, @lastMonthDate)),112)
DECLARE @MONTH9 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -9, @lastMonthDate)),112)
DECLARE @MONTH10 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -10, @lastMonthDate)),112)
DECLARE @MONTH11 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -11, @lastMonthDate)),112)
DECLARE @MONTH0 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, 0, @lastMonthDate)),112)

exec('
select * into #tam1
from
(select yearmonth, dao,product_name, col, value from #deposit_raw
cross apply
( select ''quantity'', cast(quantity as numeric) union all
  select ''balance'', cast(balance as numeric) ) c(col, value)) d
pivot
( max(value)
  for yearmonth in (['+@MONTH_1+'],['+@MONTH2+'],['+@MONTH3+'],['+@MONTH4+'],['+@MONTH5+'],['+@MONTH6+'],['+@MONTH7+'],['+@MONTH8+'],['+@MONTH9+'],['+@MONTH10+'],['+@MONTH11+'],['+@MONTH0+'])) piv;

  insert TBL_SP_DEPOSIT_EOP
  select dao, product_name, col,
  ['+@MONTH0+'] as [month0],['+@MONTH_1+'] as [month1],['+@MONTH2+'] as [month2],['+@MONTH3+'] as [month3],['+@MONTH4+'] as [month4],['+@MONTH5+'] as [month5],['+@MONTH6+'] as [month6],['+@MONTH7+'] as [month7],['+@MONTH8+'] as [month8],['+@MONTH9+'] as [month9],['+@MONTH10+'] as [month10],['+@MONTH11+'] as [month11],1 as Group_Type  from #tam1

  select * into #tam2
from
(select yearmonth, dao,product_name, col, value from #new_deposit_raw
cross apply
( select ''quantity'', cast(quantity as numeric) union all
  select ''balance'', cast(balance as numeric) ) c(col, value)) d
pivot
( max(value)
  for yearmonth in (['+@MONTH_1+'],['+@MONTH2+'],['+@MONTH3+'],['+@MONTH4+'],['+@MONTH5+'],['+@MONTH6+'],['+@MONTH7+'],['+@MONTH8+'],['+@MONTH9+'],['+@MONTH10+'],['+@MONTH11+'],['+@MONTH0+'])) piv;

  insert TBL_SP_DEPOSIT_EOP
  select dao, product_name, col,
  ['+@MONTH0+'] as [month0],['+@MONTH_1+'] as [month1],['+@MONTH2+'] as [month2],['+@MONTH3+'] as [month3],['+@MONTH4+'] as [month4],['+@MONTH5+'] as [month5],['+@MONTH6+'] as [month6],['+@MONTH7+'] as [month7],['+@MONTH8+'] as [month8],['+@MONTH9+'] as [month9],['+@MONTH10+'] as [month10],['+@MONTH11+'] as [month11],2 from #tam2
  ')

  SET @count = 0

  while (@count <12)
  begin 
  exec('
  update TBL_SP_DEPOSIT_EOP set month'+@count+'=0 where MONTH'+@count+' is null

  update TBL_SP_DEPOSIT_EOP set month'+@count+'=0 where col=''balance'' and group_type=2
  ')
  set @count = @count+1
  end
  --select * from TBL_SP_DEPOSIT_EOP
  
-- delete from  [SERVER12].[SMECustomer360].dbo.TBL_SP_DEPOSIT_EOP

--insert [SERVER12].[SMECustomer360].dbo.TBL_SP_DEPOSIT_EOP
--select * from TBL_SP_DEPOSIT_EOP

-------------------------------------------------------